package com.gxuwz.attend.dao;

import com.gxuwz.attend.entity.Classes;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//班级Dao
public class ClassesDao {
    //
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
        this.jdbcTemplate=jdbcTemplate;
    }

    /**
     * 添加班级数据
     * @param classes
     */
    public void insertClass(Classes classes){
        String sql = "insert into sys_classes(classID,className,depID,major,grade)values(?,?,?,?,?)";
        jdbcTemplate.update(sql,classes.getClassID(),classes.getClassName(),classes.getDepID(),classes.getMajor(),classes.getGrade());
    }

    /**
     * 删除班级信息
     * @param classID
     */
    public void deleteClass(String classID){
        String sql = "delete from sys_classes where classID=?";
        jdbcTemplate.update(sql,classID);
    }

    /**
     * 更新班级信息
     * @param classes
     */
    public void updateClass(Classes classes){
        String sql = "update sys_classes set className=?,depID=?,major=?,grade=? where classID=?";
        jdbcTemplate.update(sql,classes.getClassName(),classes.getDepID(),classes.getMajor(),classes.getGrade(),classes.getClassID());
    }

    /**
     * 查询全部班级信息
     * @return
     */
    public List<Classes> queryClasses(){
        String sql = "select * from sys_classes where 1=1";
        List<Object> paramValues = new ArrayList<>();
        List<Classes> classesList=new ArrayList<>();
        classesList = jdbcTemplate.query(sql, paramValues.toArray(), new RowMapper<Classes>() {
            @Override
            public Classes mapRow(ResultSet resultSet, int i) throws SQLException {
                Classes classes=new Classes();
                String classID = resultSet.getString("classID");
                String className = resultSet.getString("className");
                String depID = resultSet.getString("depID");
                String major = resultSet.getString("major");
                String grade = resultSet.getString("grade");
                classes.setClassID(classID);
                classes.setClassName(className);
                classes.setDepID(depID);
                classes.setMajor(major);
                classes.setGrade(grade);
                return classes;
            }
        });
        return classesList;
    }

    /**
     * 模糊查询班级信息
     * @param classes
     * @return
     */
    public List<Classes> queryClass(Classes classes){
        String sql = "select * from sys_classes where 1=1";
        List<Object> paramValues = new ArrayList<>();
        List<Classes> classesList=new ArrayList<>();
        if (classes!=null){
            if(classes.getClassID()!=null&&!"".equals(classes.getClassID().trim())){
                sql += " and classID=?";
                paramValues.add(classes.getClassID());
            }
            if(classes.getDepID()!=null&&!"".equals(classes.getDepID().trim())){
                sql += " and depID=?";
                paramValues.add(classes.getDepID());
            }
            if(classes.getMajor()!=null&&!"".equals(classes.getMajor().trim())){
                sql += " and major=?";
                paramValues.add(classes.getMajor());
            }
            if(classes.getGrade()!=null&&!"".equals(classes.getGrade().trim())){
                sql += " and grade=?";
                paramValues.add(classes.getGrade());
            }
            if(classes.getClassName()!=null&&!"".equals(classes.getClassName().trim())){
                sql += " and className like ?";
                paramValues.add(classes.getClassName()+"%");
            }
        }
        classesList = jdbcTemplate.query(sql, paramValues.toArray(), new RowMapper<Classes>() {
            @Override
            public Classes mapRow(ResultSet resultSet, int i) throws SQLException {
                Classes classes=new Classes();
                String classID = resultSet.getString("classID");
                String className = resultSet.getString("className");
                String depID = resultSet.getString("depID");
                String major = resultSet.getString("major");
                String grade = resultSet.getString("grade");
                classes.setClassID(classID);
                classes.setClassName(className);
                classes.setDepID(depID);
                classes.setMajor(major);
                classes.setGrade(grade);
                return classes;
            }
        });
        return classesList;
    }

}
